package com.ce.pms.charge.dao.impl;



import java.math.BigInteger;
import java.util.List;

import org.hibernate.Query;

import com.ce.pms.base.dao.impl.PmsBaseDaoImpl;
import com.ce.pms.charge.dao.IChargeVoucherDao;
import com.ce.pms.charge.model.VoucherDomain;
import com.ce.pms.charge.vo.ChargeVoucherSearchVO;
import com.ce.pms.common.annotation.AssociatedBuildingQuery;
import com.ce.pms.common.annotation.AssociatedPrecinctQuery;
import com.ce.pms.common.model.SysrefcodeDO;
import com.ce.util.BlankUtil;
import com.sitechasia.webx.core.support.Page;

public class ChargeVoucherDaoImpl extends PmsBaseDaoImpl<VoucherDomain>
		implements IChargeVoucherDao {

	/**
	 * 功能描述:查询符合条件的当前页凭证信息 <br>
	 *
	 * @param voucherSearchVO
	 * @return
	 * Create author:黄彦军 <br>
	 * Create on:2008-8-14 <br>
	 * Edit author: <br>
	 * Edit on: <br>
	 * Why: <br>
	 */
	//@AssociatedBuildingQuery(alias = "hmh", beginStr = " AND ", endStr = " ", insertAfterPoint = "WHERE 1=1",property4Building="BUILDING_ID")
	@AssociatedPrecinctQuery(alias = "fpe", beginStr = " AND ", endStr = " ", insertAfterPoint = "WHERE 1=1",property4Precinct="CHARGE_PRECINCT_ID")
	public Page queryCurrentlyPageVoucher(ChargeVoucherSearchVO voucherSearchVO) {

		String voucherNum = voucherSearchVO.getVoucherNum();   // 收费凭证号
		String houseNumber = voucherSearchVO.getHouseNumber(); // 房号
		String building  = voucherSearchVO.getBuilding();      // 楼栋
		String period = voucherSearchVO.getPeriod();           // 收费周期
		String payMethod = voucherSearchVO.getPayMethod();     // 缴费方式
		String statuz = voucherSearchVO.getStatuz();           // 凭证状态
		String participantName = voucherSearchVO.getParticipantName(); // 业主姓名
		String sortStr = voucherSearchVO.getSortStr();//排序字典

		int i = 0;
		if (!BlankUtil.isBlank(voucherNum) ){i++;}
		if (!BlankUtil.isBlank(houseNumber) ){i++;}
		if (!BlankUtil.isBlank(building) ){i++;}
		if (!BlankUtil.isBlank(period) ){ i++; }
		if (!BlankUtil.isBlank(payMethod) ){i++;}
		if (!BlankUtil.isBlank(statuz) ){i++;}
		if (!BlankUtil.isBlank(participantName)){i++;}

		Object [] par = new Object[i];
		int j =0;
		StringBuffer sql = new StringBuffer();

		sql.append(" select vou.VOUCHER_ID,");
		sql.append(" hmb.NAME,");
		sql.append(" vou.HOUSE_ID, ");
		sql.append(" hmh.HOUSE_NUMBER, ");
		sql.append(" fpe.PERIOD, ");
		sql.append(" vou.VOUCHER_NUM, ");
		sql.append(" cpa.PARTICIPANT_NAME, ");
		sql.append(" vou.CHARGE_DATE, ");
		sql.append(" ( ");
		sql.append(" select src.SYS_REF_NAME ");//从数据字典表中查出缴费方式名称
		sql.append(" from SYS_REF_CODE src ");
		sql.append(" WHERE src.SYS_REF_CODE = 'FEE_BUSINESS_CODE_PAY_METHOD' and ");
		sql.append(" src.SYS_REF_VALUE = fbc.PAY_METHOD) ");
		sql.append(" PAYMETHOD, ");
		sql.append(" vou.SUM, ");
		sql.append(" ( ");
		sql.append(" select src.SYS_REF_NAME "); //从数据字典表中查出凭证状态
		sql.append(" from SYS_REF_CODE src ");
		sql.append(" WHERE src.SYS_REF_CODE = 'FEE_VOUCHER_STATUZ' and ");
		sql.append("       src.SYS_REF_VALUE = vou.STATUZ) ");
		sql.append(" VOUSTATUZ , vou.PRO_RIGHT_ID ");
		sql.append(" from FEE_VOUCHER vou ");
		sql.append("  left join HM_HOUSE hmh on vou.HOUSE_ID = hmh.HOUSE_ID ");
		sql.append("  left join HM_BUILDING hmb on hmh.BUILDING_ID = hmb.BUILDING_ID ");
		sql.append("  left join CS_CLIENT_R_HOUSE ccr on hmh.HOUSE_ID = ccr.HOUSE_ID ");
		sql.append("  left join CS_CLIENT ccl on ccr.PARTICIPANT_ROLE_ID = ccl.CLIENT_ID ");
		sql.append("  left join CS_PARTICIPANT_ROLE cpr on ccl.CLIENT_ID = cpr.PARTICIPANT_ROLE_ID ");
		sql.append("  left join CS_PARTICIPANT cpa on cpr.PARTICIPANT_ID = cpa.PARTICIPANT_ID ");
		sql.append("  left join FEE_TRANSACTION ftr on vou.VOUCHER_ID = ftr.VOUCHER_ID ");
		sql.append("  left join FEE_PERIOD fpe on ftr.PERIOD_ID = fpe.PERIOD_ID ");
		sql.append("  left join FEE_BUSINESS_CODE fbc on ftr.BIZ_CODE_ID = fbc.BIZ_CODE_ID ");
		sql.append("  left join FEE_SUB_ACCOUNT acc on acc.SUB_ACCOUNT_ID = ftr.SUB_ACCOUNT_ID ");
		sql.append(" WHERE 1=1 AND acc.ACCOUNT_TYPE = '"+ SysrefcodeDO.FEE_SUB_ACCOUNT_ACCOUNT_TYPE_CHARGE +"'") ;
		sql.append(" and ftr.CLAZZ_CODE <> '"+SysrefcodeDO.FEE_TRANSACTION_CLAZZ_SUBSIDIARYPAY+"'  ");

		//加入房号查询条件
		if (!BlankUtil.isBlank( houseNumber)){
				sql.append(" and hmh.HOUSE_NUMBER like ? ");
			par[j] = "%"+houseNumber+"%";
			j++;
		}
		//加入楼栋查询条件
		if (!BlankUtil.isBlank(building)){
				sql.append(" and hmb.BUILDING_ID = ? ");
			par[j] = building;
			j++;
		}
		//加入收费凭证号查询条件
		if (!BlankUtil.isBlank(voucherNum)){
				sql.append(" and vou.VOUCHER_NUM like ? ");
			par[j] = "%"+voucherNum+"%";
			j++;
		}
		//加入凭证状态查询条件
		if (!BlankUtil.isBlank(statuz)){
				sql.append("  and vou.STATUZ = ? ");
			par[j] = statuz;
			j++;
		}
		//加入业主查询条件
		if (!BlankUtil.isBlank(participantName)){
				sql.append("  and cpa.PARTICIPANT_NAME like ?");
			par[j]="%"+participantName+"%";
			j++;
		}
		//加入收费周期条件
		if (!BlankUtil.isBlank(period)){
				sql.append(" and fpe.PERIOD = ?  ");
			par[j] = period;
			j++;
		}
		//加入缴费方式查询条件
		if (!BlankUtil.isBlank(payMethod)){
				sql.append(" and fbc.PAY_METHOD = ? ");
			par[j] = payMethod;
			j++;
		}
		sql.append(" group by vou.VOUCHER_ID ");
		sql.append(" order by  "+ sortStr);

		Page page = super.pagedSqlQueryByPageNo(sql.toString(),voucherSearchVO.getPageNo(), voucherSearchVO.getPageSize(),par);
		return page;
	}

	/**
	 * 功能描述:查找打印总数  <br>
	 * @return
	 * Create author:黄彦军 <br>
	 * Create on:2008-8-22  <br>
	 * Edit author:  <br>
	 * Edit on:    <br>
	 * Why:     <br>
	 */
	public List findPrintTotal() {

		String queryStr = "SELECT SUM(vou.printTimes) FROM VoucherDomain vou ";
		Query query = createQuery(queryStr, null);
		return query.list();
	}

	/**
	 * 功能描述:根据参数代码查询 参数值  <br>
	 * @param pkey
	 * @return
	 * Create author:黄彦军 <br>
	 * Create on:2008-10-9  <br>
	 * Edit author:  <br>
	 * Edit on:    <br>
	 * Why:     <br>
	 */
	public Long findPvalueByPkey(String pkey){
		String queryStr = "SELECT SPD.PVALUE FROM SEC_PARAMETER_DEF SPD WHERE GKEY='4' AND SPD.PKEY =  ? ";
		Query query = createSQLQuery(queryStr, new Object[]{pkey});
		List list = query.list();
		if (BlankUtil.isBlank(list))
			return new Long(0);
		return new Long(list.get(0).toString());
	}

	/**
	 * 功能描述:根据指定编号前缀当天最大的流水序号<br>
	 * @param prifix
	 * @return<br>
	 * Create author:罗军林<br>
	 * Create on:2008-9-28<br>
	 * Edit author:<br>
	 * Edit on:<br>
	 * Why:<br>
	 */
	public int getMaxVoucherNumSeqByType(String prifix){
		StringBuffer sql=new StringBuffer();
		sql.append("SELECT  MAX(cast(SUBSTRING_INDEX(VOUCHER_NUM,'-',-1)  as  SIGNED )) FROM FEE_VOUCHER ")
		.append(" WHERE CAST(CHARGE_DATE as Date)=CAST(now() as Date) ")
		.append(" and SUBSTRING_INDEX(VOUCHER_NUM,'-',1)=? ");
		Query query=createSQLQuery(sql.toString(), prifix);
		Object result=query.uniqueResult();
		return result==null ? 0 : ((BigInteger)result).intValue();
	}

	/**
	 * 功能描述:根据 主交易ID 查询收费凭证打印信息  <br>
	 * @param transId 主交易ID
	 * @return
	 * Create author:黄彦军 <br>
	 * Create on:2008-10-10  <br>
	 * Edit author:  <br>
	 * Edit on:    <br>
	 * Why:     <br>
	 */
	public List getChanagVoucherInfo(Long transId){

		StringBuffer sql = new StringBuffer();

		sql.append(" SELECT SUM(TRN.DEBIT) AS DEBIT, ");
		sql.append("   IFNULL(ITE.ITEM_NAME, '滞纳金') AS ITEM_NAME, ");
		sql.append("   IFNULL(ITE.SEQ_NO, 99999999) AS SEQ, ");
		sql.append("   HH.HOUSE_NUMBER, ");
		sql.append("   GROUP_CONCAT( DISTINCT  IF(TRN.BIZ_CODE_ID IN (SELECT PVALUE  FROM SEC_PARAMETER_DEF WHERE GKEY='4' AND (PKEY = 'SEWAGE_CODE' OR PKEY ='RUBBISH_CODE'  OR PKEY='LATEFEE_CODE')), NULL, FCI.METER_CLASS)) AS METER_CLASS ");
		sql.append(" FROM FEE_TRANSACTION AS TRN ");
		sql.append(" LEFT JOIN FEE_CHARGE_ITEM AS ITE ON ITE.BIZ_CODE_ID = TRN.BIZ_CODE_ID ");
		sql.append(" LEFT JOIN FEE_TRA_R_TRA AS FTT ON TRN.TRANSACTION_ID =FTT.TRANSACTION_ID2 ");
		sql.append(" LEFT JOIN FEE_STA_VERSION AS FSV ON FSV.STA_HIS_ID = TRN.STA_HIS_ID ");
		sql.append(" LEFT JOIN FEE_CHAR_STA AS FCS ON FCS.CHARGE_STA_ID = FSV.CHARGE_STA_ID ");
		sql.append(" LEFT JOIN FEE_CHARGE_ITEM AS FCI ON FCI.CHARGE_ITEM_ID = FCS.CHARGE_ITEM_ID ");
		sql.append(" LEFT JOIN SEC_PARAMETER_DEF AS SPD ON (SPD.GKEY='4' AND SPD.PVALUE = TRN.BIZ_CODE_ID) ");
		sql.append(" JOIN HM_HOUSE AS HH ON HH.HOUSE_ID = TRN.HOUSE_ID ");
		sql.append(" WHERE (FTT.TRANSACTION_ID1 = ? OR  ");
		sql.append("        FTT.TRANSACTION_ID1 = ANY  ");
		sql.append("          (SELECT FTRT.TRANSACTION_ID2  ");
		sql.append("       	FROM FEE_TRA_R_TRA FTRT JOIN FEE_TRANSACTION FTR ON FTRT.TRANSACTION_ID1 = FTR.TRANSACTION_ID  ");
		sql.append("        	WHERE FTRT.TRANSACTION_ID1 = ? and  FTRT.RELATION_TYPE in ('001', '002', '003', '005')  ");
		sql.append("          )  ");
		sql.append("        OR FTT.TRANSACTION_ID1 = ANY  ");
		sql.append("          (SELECT FTRT.TRANSACTION_ID2  ");
		sql.append("        	FROM FEE_TRA_R_TRA FTRT JOIN FEE_TRANSACTION FTR ON FTRT.TRANSACTION_ID1 = FTR.TRANSACTION_ID  ");
		sql.append("        	WHERE FTRT.TRANSACTION_ID1 = ANY  ");
		sql.append("              (SELECT FTRT.TRANSACTION_ID2  ");
		sql.append("       		FROM FEE_TRA_R_TRA FTRT JOIN FEE_TRANSACTION FTR ON FTRT.TRANSACTION_ID1 = FTR.TRANSACTION_ID  ");
		sql.append("        		WHERE FTRT.TRANSACTION_ID1 = ?  ");
		sql.append("               and  FTRT.RELATION_TYPE in ('001', '002', '003', '005')  ");
		sql.append("              )  ");
		sql.append("           )  ");
		sql.append("         )  ");
		sql.append(" and FTT.RELATION_TYPE in ('001', '002', '003', '005')  ");
		sql.append(" and DEBIT <> ''  ");
		sql.append(" and DEBIT is not null  ");
		sql.append(" and TRN.STATUZ = '002'  ");
		sql.append(" GROUP BY TRN.BIZ_CODE_ID  ");
		sql.append(" ORDER BY SEQ  ");

		Query query = createSQLQuery(sql.toString(), new Object[]{transId,transId,transId});
	return query.list();
}
	/**
	 * 功能描述:打印一个凭证，将打印资料加1,如果状态为新建的，则修改为已打印  <br>
	 * @param voucherID
	 * Create author:黄彦军 <br>
	 * Create on:2008-8-22  <br>
	 * Edit author:  <br>
	 * Edit on:    <br>
	 * Why:     <br>
	 */
	public void updateVoucherStart(Long voucherId){
		VoucherDomain voucherDomain =findById(voucherId);
		Short printTimes = voucherDomain.getPrintTimes();
		if (BlankUtil.isBlank(printTimes)){
			printTimes = 0;
		}
		int print = (int)printTimes;
		printTimes=Short.parseShort(((print+1)+""));  //打印次数
		String statuz =SysrefcodeDO.FEE_VOUCHER_STATUZ_NEW;  //状态
		if (statuz.equals((String)voucherDomain.getStatuz())){
			statuz=SysrefcodeDO.FEE_VOUCHER_STATUZ_PRINTED;
		}else{
			statuz = voucherDomain.getStatuz();
		}
		String hql = "update VoucherDomain vo set vo.printTimes = ? ,vo.statuz = ?  where vo.voucherId = ? ";
		Query query = this.createQuery(hql, new Object[]{printTimes,statuz,voucherId});
		query.executeUpdate();
	}

}
